package com.uni.dao;

import com.uni.entity.Commodity;
import com.uni.entity.CommodityType;
import com.uni.service.Service;
import com.uni.utils.DruidUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * --------------project-------------
 *
 * @Author Unirithe  - Mr.Chao
 * - CSDN:
 * https://blog.csdn.net/Unirithe
 * - GitHub:
 * https://github.com/unirithe
 * @create 2021/12/20 18:34
 * ------------------------------------------
 */
public class CommodityDaoImpl implements CommodityDao{

    @Override
    public List<CommodityType> queryAllType() {
        List<CommodityType> list = new ArrayList<>();
        Connection connection = DruidUtil.getConnection();
        String sql = "select * from commodity_type";
        try {
            PreparedStatement pstmt = connection.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()){
                list.add(new CommodityType(rs.getInt(1), rs.getString(2)));
            }
            DruidUtil.release(connection, pstmt, rs);
        } catch (Exception e){
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public String queryTypeById(int id) {
        String result = null;
        Connection connection = DruidUtil.getConnection();
        String sql = "select info from commodity_type where id = ?";
        try{
            PreparedStatement pstmt = connection.prepareStatement(sql);
            pstmt.setInt(1, id);
            ResultSet resultSet = pstmt.executeQuery();
            while(resultSet.next())
                result = resultSet.getString(1);
            DruidUtil.release(connection, pstmt, resultSet);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return result;
    }

    @Override
    public boolean insertCommodity(Commodity commodity) {
        boolean ok = false;
        Connection connection = DruidUtil.getConnection();
        String sql = "insert into commodity (tid, uid, name, info, img, price) values(?, ?, ?, ?, ?,?)";
        try{
            PreparedStatement pstmt = connection.prepareStatement(sql);
            pstmt.setInt(1, commodity.getTid());
            pstmt.setInt(2, commodity.getUid());
            pstmt.setString(3, commodity.getName());
            pstmt.setString(4, commodity.getInfo());
            pstmt.setString(5, commodity.getImg());
            pstmt.setFloat(6, commodity.getPrice());
            ok = pstmt.execute();
            DruidUtil.release(connection, pstmt, null);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return ok;
    }

    @Override
    public List<Commodity> queryAll() {
        List<Commodity> list = new ArrayList<>();
        Connection connection = DruidUtil.getConnection();
        String sql = "select * from commodity";
        try{
            PreparedStatement pstmt = connection.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next())
                list.add(new Commodity(rs.getInt(1), rs.getInt(2), rs.getInt(3),rs.getString(4)
                        ,rs.getString(5), rs.getString(6), rs.getFloat(7)));
            DruidUtil.release(connection, pstmt, rs);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }

    @Override
    public List<Commodity> query(int begin, int end, int tid) {
        List<Commodity> list = new ArrayList<>();
        Connection connection = DruidUtil.getConnection();
        String sql;
        if(tid == -1)
            sql = "select * from commodity limit ?, ?";
        else
            sql = "select * from commodity where tid=? limit ?, ? ";
        try{
            PreparedStatement pstmt = connection.prepareStatement(sql);
            if(tid == -1){
                pstmt.setInt(1, begin);
                pstmt.setInt(2, end);
            } else {
                pstmt.setInt(1, tid);
                pstmt.setInt(2, begin);
                pstmt.setInt(3, end);
            }
            ResultSet rs = pstmt.executeQuery();
            while(rs.next())
                list.add(new Commodity(rs.getInt(1), rs.getInt(2), rs.getInt(3),rs.getString(4)
                        ,rs.getString(5), rs.getString(6), rs.getFloat(7)));
            DruidUtil.release(connection, pstmt, rs);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }

    @Override
    public Commodity query(int uid) {
        Commodity commodity = null;
        Connection connection = DruidUtil.getConnection();
        String sql = "select * from commodity where id = ?";
        try{
            PreparedStatement pstmt = connection.prepareStatement(sql);
            pstmt.setInt(1, uid);
            ResultSet rs = pstmt.executeQuery();

            while(rs.next())
                commodity = new Commodity(rs.getInt(1),
                        rs.getInt(2),
                        rs.getInt(3),
                        rs.getString(4),
                        rs.getString(5),
                        rs.getString(6),
                        rs.getFloat(7));
            commodity.setTypeName(Service.commodity.queryTypeById(commodity.getTid()));
            commodity.setUserName(Service.user.queryUserById(commodity.getUid()).getName());

            DruidUtil.release(connection, pstmt, rs);
        } catch (Exception e){
            e.printStackTrace();
        }
        return commodity;
    }

    @Override
    public List<Commodity> queryByUid(int uid) {
        List<Commodity> list = new ArrayList<>();
        Connection connection = DruidUtil.getConnection();
        String sql = "select * from commodity where uid = ?";
        try{
            PreparedStatement pstmt = connection.prepareStatement(sql);
            pstmt.setInt(1, uid);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()){
                Commodity commodity = new Commodity(
                        rs.getInt(1),
                        rs.getInt(2),
                        rs.getInt(3),
                        rs.getString(4),
                        rs.getString(5),
                        rs.getString(6),
                        rs.getFloat(7));
                commodity.setTypeName(Service.commodity.queryTypeById(commodity.getTid()));
                commodity.setUserName(Service.user.queryUserById(commodity.getUid()).getName());
                list.add(commodity);
            }
            DruidUtil.release(connection, pstmt, rs);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public int count() {
        int result = 0;
        Connection connection = DruidUtil.getConnection();
        String sql = "select count(*) from commodity";
        try{
            PreparedStatement pstmt = connection.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next())
                result = rs.getInt(1);
            DruidUtil.release(connection, pstmt, rs);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return result;
    }

    @Override
    public int countByTypeId(int id) {
        int result = 0;
        Connection connection = DruidUtil.getConnection();
        String sql;
        if(id == -1)
            sql = "select count(*) from commodity";
        else
            sql = "select count(*) from commodity where tid = ?";
        try{
            PreparedStatement pstmt = connection.prepareStatement(sql);
            if(id!=-1) pstmt.setInt(1, id);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next())
                result = rs.getInt(1);
            DruidUtil.release(connection, pstmt, rs);
        } catch (Exception e){
            e.printStackTrace();
        }
        return result;
    }

    @Override
    public boolean insertAction(int uid, int cid) {
        Connection connection = DruidUtil.getConnection();
        String sql = "insert into user_action (uid, cid) values (?, ?) on duplicate key update time=?";
        boolean ok = false;
        try{
            PreparedStatement pstmt = connection.prepareStatement(sql);
            pstmt.setInt(1, uid);
            pstmt.setInt(2, cid);
            pstmt.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
            ok = pstmt.execute();
            DruidUtil.release(connection, pstmt, null);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return ok;
    }

    @Override
    public boolean deleteCommodity(int cid) {
        Connection connection = DruidUtil.getConnection();
        String sql1 = "delete from user_action where cid = ?";
        String sql2 = "delete from commodity where id = ?";
        try{
            PreparedStatement pstmt = connection.prepareStatement(sql1);
            pstmt.setInt(1, cid);
            pstmt.execute();
            pstmt = connection.prepareStatement(sql2);
            pstmt.setInt(1, cid);
            pstmt.execute();
            DruidUtil.release(connection, pstmt, null);
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }
}
